package com.smartwebx.fr.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.List;

import com.aspose.cells.BackgroundType;
import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.CellsHelper;
import com.aspose.cells.Color;
import com.aspose.cells.Font;
import com.aspose.cells.HTMLLoadOptions;
import com.aspose.cells.License;
import com.aspose.cells.LoadFormat;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Style;
import com.aspose.cells.Workbook;
import com.aspose.cells.WorkbookDesigner;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;
import com.google.common.base.Splitter;
import com.google.common.collect.Iterables;
import com.google.common.collect.Lists;
import com.google.common.primitives.Ints;

public class CellTest {

	public static void main(String[] args) throws Exception {
		try {
			InputStream is = CellTest.class.getClassLoader().getResourceAsStream("\\aspose_excel_license.xml");
			License aposeLic = new License();
			aposeLic.setLicense(is);
			// is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// int int1 = Integer.parseInt("1.22");
		// System.out.println(int1);
		// // test2();
		// String cellname = CellsHelper.cellIndexToName(100, 0);
		// System.out.println("Cell Name at [0, 0]: " + cellname);
		//
		// cellname = CellsHelper.cellIndexToName(4, 0);
		// System.out.println("Cell Name at [4, 0]: " + cellname);
		//
		// cellname = CellsHelper.cellIndexToName(0, 4);
		// System.out.println("Cell Name at [0, 4]: " + cellname);
		//
		// cellname = CellsHelper.cellIndexToName(2, 2);
		// System.out.println("Cell Name at [2, 2]: " + cellname);
		List<List<String>> allLines = Lists.newArrayList();

		List<String> lines = Lists.newArrayList("站码::2::0", "站名::2::0", "最大1小时::0::5", "最大3小时::0::5", "最大6小时::0::5",
				"最大12小时::0::5", "最大24小时::0::5");
		allLines.add(lines);
		lines = Lists.newArrayList("雨量::0::0", "时间::0::0", "历史排位::0::0", "历史雨量::0::0", "历史时间::0::0", "雨量::0::0",
				"时间::0::0", "历史排位::0::0", "历史雨量::0::0", "历史时间::0::0", "雨量::0::0", "时间::0::0", "历史排位::0::0",
				"历史雨量::0::0", "历史时间::0::0", "雨量::0::0", "时间::0::0", "历史排位::0::0", "历史雨量::0::0", "历史时间::0::0",
				"雨量::0::0", "时间::0::0", "历史排位::0::0", "历史雨量::0::0", "历史时间::0::0");
		allLines.add(lines);

		test4(allLines);
		// test5();
	}

	private static void test4(List<List<String>> allLines) throws Exception {
		Workbook workbook = new Workbook();
		Worksheet sheet = workbook.getWorksheets().get(0);
		Cells cells = sheet.getCells();

		int row = 0;
		for (List<String> lines : allLines) {
			int col = 0;
			for (String point : lines) {
				Iterable<String> pointSplitter = Splitter.on("::").split(point);
				String text = Iterables.get(pointSplitter, 0);
				Integer rowspan = Ints.tryParse(Iterables.get(pointSplitter, 1));
				Integer colspan = Ints.tryParse(Iterables.get(pointSplitter, 2));

				for (;;) {
					String cellName = CellsHelper.cellIndexToName(row, col);
					Cell cell = cells.get(cellName);
					if (cell.isMerged()) {
						col++;
					} else {
						cell.setValue(text);
						cells.merge(row, col, rowspan == 0 ? 1 : rowspan, colspan == 0 ? 1 : colspan);
						break;
					}
				}
				col++;
			}
			row++;
		}

		// Cell cell = cells.get("A3");
		// cell.setValue("TTTTTTTTTTTTT");
		// cells.merge(2, 0, 3, 4);

		workbook.save("table.xlsx");
	}

	private static void test5() throws FileNotFoundException, Exception {
		Workbook workbook = new Workbook();
		Worksheet sheet = workbook.getWorksheets().get(0);
		Cells cells = sheet.getCells();
		String cellName = CellsHelper.cellIndexToName(0, 0);
		Cell cell = cells.get(cellName);
		cell.setValue("abcdef");
		cells.merge(0, 0, 5, 1);
		workbook.save("table.xlsx");
	}

	private static void test3() throws Exception {
		// Instantiating a Workbook object
		Workbook workbook = new Workbook();

		// Obtaining the reference of the newly added worksheet by passing its
		// sheet index
		int sheetIndex = workbook.getWorksheets().add();
		Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);

		// Creating an array containing names as string values
		String[] names = new String[] { "laurence chen", "roman korchagin", "kyle huang" };
		String[] names1 = new String[] { "laurence chen1", "roman korchagin2", "kyle huang3" };
		String[] names2 = new String[] { "laurence chen1", "roman korchagin2", "kyle huang3" };

		String[][] data = new String[][] { names, names1, names2 };
		// Importing the array of names to 1st row and first column vertically
		Cells cells = worksheet.getCells();
		cells.importArray(data, 0, 0);
		// Saving the Excel file
		workbook.save("d:\\DataImport.xlsx");
	}

	private static void test2() throws Exception {
		// Instantiating a Workbook object
		Workbook workbook = new Workbook();

		// Accessing the added worksheet in the Excel file
		int sheetIndex = workbook.getWorksheets().add();
		Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
		Cells cells = worksheet.getCells();

		// Adding a string value to the cell
		Cell cell = cells.get("A1");
		cell.setValue("Hello World");

		// Adding a double value to the cell
		cell = cells.get("A2");
		cell.setValue(20.5);

		// Adding an integer value to the cell
		cell = cells.get("A3");
		cell.setValue(15);

		// Adding a boolean value to the cell
		cell = cells.get("A4");
		cell.setValue(true);

		// Adding a date/time value to the cell
		cell = cells.get("A5");
		cell.setValue(Calendar.getInstance());

		// Setting the display format of the date
		// Style style = cell.getStyle();
		// style.setNumber(15);
		// cell.setStyle(style);

		// Saving the Excel file
		workbook.save("d:\\output.xls");

	}

	private static void test1() throws Exception {
		// Create a Workbook.
		Workbook wbk = new Workbook();

		// Create a Worksheet and get the first sheet.
		Worksheet worksheet = wbk.getWorksheets().get(0);

		// Create a Cells object to fetch all the cells.
		Cells cells = worksheet.getCells();

		// Merge some Cells (C6:E7) into a single C6 Cell.
		cells.merge(5, 2, 2, 3);

		// Input data into C6 Cell.
		worksheet.getCells().get(5, 2).setValue("This is my value");

		// Create a Style object to fetch the Style of C6 Cell.
		Style style = worksheet.getCells().get(5, 2).getStyle();

		// Create a Font object
		Font font = style.getFont();

		// Set the name.
		font.setName("Times New Roman");

		// Set the font size.
		font.setSize(18);

		// Set the font color
		font.setColor(Color.getBlue());

		// Bold the text
		font.setBold(true);

		// Make it italic
		font.setItalic(true);

		// Set the backgrond color of C6 Cell to Red
		style.setForegroundColor(Color.getRed());
		style.setPattern(BackgroundType.SOLID);
		// Apply the Style to C6 Cell.
		cells.get(5, 2).setStyle(style);

		// Save the Workbook.
		wbk.save("d:\\mergingcells.xls");
	}
}
